IF DB_ID('OnlineShop') is not null
DROP DATABASE OnlineShop
GO
CREATE DATABASE OnlineShop
GO
USE OnlineShop
GO
-------------------------------------------USER TABLE
CREATE TABLE Staffs
(
	StaffID int identity(1,1) primary key,
	SUserName nvarchar(50) not null,
	SPassWord nvarchar(50) not null,
	SEmail nvarchar(200) not null,
	SFullName nvarchar(200) not null,
	SAddress nvarchar(500),
	SPermission bit not null,   -- Admin || Employee
	SSecretQuestion nvarchar(200) not null,
	SSecretAnswer nvarchar(200) not null,
)
GO
CREATE TABLE Customers
(
	CusID int identity(1,1) primary key,
	CUserName nvarchar(50) not null,
	CPassWord nvarchar(50) not null,
	CEmail nvarchar(200) not null,
	CFullName nvarchar(200) not null,
	CAddress nvarchar(500),
	CPhoneNo nvarchar(20),
	CSecretQuestion nvarchar(200) not null,
	CSecretAnswer nvarchar(200) not null,
	
	CusVPP money,	
)
GO
----------------------------------------------PRODUCT TABLE
CREATE TABLE Categories
(
	CateID varchar(2) primary key,
	CateName nvarchar(200) not null,
	CateDescription nvarchar(max),
)
GO
CREATE TABLE Products
(
	ProID varchar(7) primary key,   --2 first digit CategoryID, 5 digit auto generate
	CateID varchar(2) references Categories(CateID),
	ProName nvarchar(200) not null,
	ProImage nvarchar(max) not null,
	ProDescription nvarchar(max),
	ProPriceMoney money default(0) not null,
	ProPriceVPP money default(0) not null,
	ProQuantity int check(ProQuantity >= 0) default(0) not null,
	ProWarranty nvarchar(100) not null,  --bao hanh
	ProAvailable bit not null,
)
GO
----------------------------------------------ORDER
GO
CREATE TABLE TypeOfPayment
(
	PaymentID int check(len(PaymentID) = 1) primary key,
	PaymentDescription nvarchar(200), 
)

GO
CREATE TABLE [Order]
(
	OrderNumber varchar(8) primary key,   --8 Digits auto generate
	CusID int references Customers(CusID),
	PaymentID int references TypeOfPayment(PaymentID),  --1 digit
	OrderStatus varchar(10) default('Waitting') not null,      --Select history by status  (Watting,Done,Cancel)
	TotalPrice money check(TotalPrice > 0) not null,
	OrderDate datetime default(getdate()) not null,  --ngay dat hang
	PaymentDate datetime not null, --ngay thanh toan
	ConfirmOrderCode uniqueidentifier not null,  -- auto generate, xac nhan da thanh toan hay chua,, sau do update status
)
GO
CREATE TABLE OrderDetails
(
	OrderNumber varchar(8) references [Order](OrderNumber),
	ProID varchar(7) references Products(ProID),
	ProBuyQuantity int default(1) not null,
	barcode varchar(16) not null,  --16 digits
)
--------------------------------------------------FEEDBACK
GO
CREATE TABLE FeedBack
(
	FeedBackID int identity(1,1) primary key,
	FeedBackDate datetime default(getdate()),
	CusID int references Customers(CusID),
	FeedBackSub nvarchar(300) not null,  -- tieu de
	Content nvarchar(max) not null,  --noi dung
	FBStatus nvarchar(20) default('Watting') not null,  --trang thai cho` tra loi hoac da tra loi (Done)
)
GO
--------------------------------------------------CONVERSION and HISTORY DEPOSIT VPP
CREATE TABLE Conversion
(
	MoneyType nvarchar(5) primary key not null,
	RealMoney money not null,
	VPP money not null,
)
GO
CREATE TABLE DepositVPP
(
	CusID int references Customers(CusID),
	DateTransfer datetime default(getdate()) not null,
	MoneyType nvarchar(5) references Conversion(MoneyType),
	AmountMoney money not null,
	AmountVPP money not null,
	DepositStatus nvarchar(10) default('Watting') not null, --Watting or Done
	ConfirmDepositCode uniqueidentifier not null, -- auto generate, xac nhan chuyen tien hay chua, sau do update status
)